
import { log } from 'console';
import * as Excel from 'exceljs';
import { isNil } from 'lodash';
import { DataSource } from "typeorm"
/* import { intersection, isNumber } from 'lodash';
import { isNil } from 'lodash'; */




// 更新终端文件
async function updateterminal() {
    let AppDataSource;
    let querier: any;
    try {
        AppDataSource = new DataSource({
            type: "mysql",
            host: "localhost",
            port: 3308,
            username: "zht",
            password: "zht780214",
            database: "capital_pena",
            entities: []
        })

        await AppDataSource.initialize()
            .then(() => {
                console.log("Data Source has been initialized!")
            })
            .catch((err) => {
                console.error("Error during Data Source initialization", err)
            })
        querier = AppDataSource.createQueryRunner();
        await querier.connect();
        await querier.startTransaction();   // 读未提交数据        
        const r = await querier.query("select id, name from base_capitalowner");
        const owners = new Map();
        for (let index = 0; index < r.length; index++) {
            const element = r[index];
            owners.set(element.name, element.id);
        }
        const workbook_source = new Excel.Workbook();
        // const workbook_target = new Excel.Workbook();
        await workbook_source.xlsx.readFile(`C:\\Users\\zhangtao\\Desktop\\111\\固定资产明细.xlsx`);
        // await workbook_target.xlsx.readFile(`C:\\Users\\zhangtao\\Desktop\\111\\终端.xlsx`);
        const sourceSheet = workbook_source.getWorksheet('Sheet1');
        const sourceCount = sourceSheet.rowCount;
        for (let index = 2; index <= sourceCount; index++) {
            // let find = false;
            const sourceRow = sourceSheet.getRow(index);
            // const sourceId = sourceRow.getCell('D').value.toString().trim();
            let sourceId: string;
            const sourceObj = sourceSheet.getCell(`D${index}`).value;
            const sourceOwner = sourceRow.getCell('A').value.toString().trim();
            const sourceSapno = sourceRow.getCell('B').value.toString().trim();
            if (isNil(sourceObj)) {
                continue;
            }
            if (typeof sourceObj === 'string') {
                sourceId = sourceObj.toString().trim();
            } else {
                sourceId = richTextToText(sourceObj['richText']);
            }
            
            const ownerId = owners.get(sourceOwner);
            if (ownerId === undefined) {
                console.log(`${sourceOwner}不存在`);
                continue;
            }
            console.log(sourceId, ownerId, sourceSapno);
            
            const updateSql = `update capital set sapno = '${sourceSapno}', owner = ${ownerId} where identifier = '${sourceId}';`
            console.log(updateSql);
            
            const r1 = await querier.query(updateSql);
            console.log(r1);
            
        }
        await querier.commitTransaction();
        // await workbook_target.xlsx.writeFile(`C:\\Users\\zhangtao\\Desktop\\111\\终端1.xlsx`);
    } catch (err) {
        await querier.rollbackTransaction();
        console.log(err);
    } finally {
        
    }
}

function getRichTextFromCell(worksheet, cellAddress) {
    const cell = worksheet.getCell(cellAddress);
    return cell.richText;
}

function richTextToText(richTextArray) {
    let plainText = '';
    if (richTextArray && Array.isArray(richTextArray)) {
        richTextArray.forEach((richTextFragment) => {
            plainText += richTextFragment.text;
        });
    }
    return plainText;
}

// updateterminal();

async function updateplace() {
    let AppDataSource;
    let querier: any;
    try {
        AppDataSource = new DataSource({
            type: "mysql",
            host: "localhost",
            port: 3308,
            username: "zht",
            password: "zht780214",
            database: "capital_pena",
            entities: []
        })

        await AppDataSource.initialize()
            .then(() => {
                console.log("Data Source has been initialized!")
            })
            .catch((err) => {
                console.error("Error during Data Source initialization", err)
            })
        querier = AppDataSource.createQueryRunner();
        await querier.connect();
        await querier.startTransaction();   // 读未提交数据        
        
        const workbook_source = new Excel.Workbook();
        // const workbook_target = new Excel.Workbook();
        await workbook_source.xlsx.readFile(`C:\\Users\\zhangtao\\Desktop\\111\\财商中心.xlsx`);
        // await workbook_target.xlsx.readFile(`C:\\Users\\zhangtao\\Desktop\\111\\终端.xlsx`);
        const sourceSheet = workbook_source.getWorksheet('Sheet1');
        const sourceCount = sourceSheet.rowCount;
        for (let index = 2; index <= sourceCount; index++) {
            // let find = false;
            const sourceRow = sourceSheet.getRow(index);
            // const sourceId = sourceRow.getCell('D').value.toString().trim();
            let sourceId: string;
            let sourcePlace: string;
            const sourceObj = sourceSheet.getCell(`A${index}`).value;

            const sourcePlaceObj = sourceRow.getCell('B').value;

            if (typeof sourcePlaceObj === 'string') {
                sourcePlace = sourcePlaceObj.toString().trim();
            } else {
                sourcePlace = richTextToText(sourcePlaceObj['richText']);
            }
            if (typeof sourceObj === 'string') {
                sourceId = sourceObj.toString().trim();
            } else {
                sourceId = richTextToText(sourceObj['richText']);
            }
            const updateSql = `update capital set place = '${sourcePlace}' where identifier = '${sourceId}';`
            console.log(updateSql);
            
            const r1 = await querier.query(updateSql);
            console.log(r1);
            
        }
        await querier.commitTransaction();
        // await workbook_target.xlsx.writeFile(`C:\\Users\\zhangtao\\Desktop\\111\\终端1.xlsx`);
    } catch (err) {
        await querier.rollbackTransaction();
        console.log(err);
    } finally {
        
    }
}

async function checkcaiwu() {
    let AppDataSource;
    let querier: any;
    try {
        AppDataSource = new DataSource({
            type: "mysql",
            host: "localhost",
            port: 3308,
            username: "zht",
            password: "zht780214",
            database: "capital_pena",
            entities: []
        })

        await AppDataSource.initialize()
            .then(() => {
                console.log("Data Source has been initialized!")
            })
            .catch((err) => {
                console.error("Error during Data Source initialization", err)
            })
        querier = AppDataSource.createQueryRunner();
        await querier.connect();
        await querier.startTransaction();   // 读未提交数据        
        const r_owners = await querier.query("select id, name from base_capitalowner");
        const owners = new Map();
        for (let index = 0; index < r_owners.length; index++) {
            const element = r_owners[index];
            owners.set(element.name, element.id);
        }
        const r_stats = await querier.query("select id, name from base_capitalstate");
        const stats = new Map();
        for (let index = 0; index < r_stats.length; index++) {
            const element = r_stats[index];
            stats.set(element.id, element.name);
        }
        const r_departs = await querier.query("select id, name from base_department");
        const departs = new Map();
        for (let index = 0; index < r_departs.length; index++) {
            const element = r_departs[index];
            departs.set(element.id, element.name);
        }
        // console.log(departs);
        // return;
        const workbook_source = new Excel.Workbook();
        // const workbook_target = new Excel.Workbook();
        await workbook_source.xlsx.readFile(`C:\\Users\\zhangtao\\Desktop\\111\\固定资产明细-新疆分.xlsx`);
        // await workbook_target.xlsx.readFile(`C:\\Users\\zhangtao\\Desktop\\111\\终端.xlsx`);
        const sourceSheet = workbook_source.getWorksheet('Sheet1');
        const sourceCount = sourceSheet.rowCount;
        for (let index = 2; index <= sourceCount; index++) {
            // let find = false;
            const sourceRow = sourceSheet.getRow(index);
            // const sourceId = sourceRow.getCell('D').value.toString().trim();
            let sourceId: string;
            // const sourceObj = sourceSheet.getCell(`D${index}`).value;
            const sourceOwner = sourceRow.getCell('B').value.toString().trim();
            const sourceSapno = sourceRow.getCell('E').value.toString().trim();
            if (isNil(sourceSapno)) {
                continue;
            }
            /* if (isNil(sourceObj)) {
                continue;
            } */
            /* if (typeof sourceObj === 'string') {
                sourceId = sourceObj.toString().trim();
            } else {
                sourceId = richTextToText(sourceObj['richText']);
            } */
            
            const ownerId = owners.get(sourceOwner);
            if (ownerId === undefined) {
                // console.log(`${sourceOwner}不存在`);
                continue;
            }
            // console.log(sourceId, ownerId, sourceSapno);
            const selectSql = `select state, department, user from capital where owner = ${ownerId} and sapno = '${sourceSapno}';`;

            // const updateSql = `update capital set sapno = '${sourceSapno}', owner = ${ownerId} where identifier = '${sourceId}';`
            // console.log(updateSql);
            
            const r1 = await querier.query(selectSql);
            if (r1.length > 1) {
                console.log('大于一个', sourceSapno);
                
            }
            console.log(r1);
            if (r1[0]) {
                // console.log(stats.get(r1[0]['state']));                
                sourceRow.getCell('F').value = stats.get(r1[0]['state']);
                sourceRow.getCell('G').value = departs.get(r1[0]['department']);
                sourceRow.getCell('H').value = r1[0]['user'];
            }
            
            // break;
        }
        await querier.commitTransaction();
        await workbook_source.xlsx.writeFile('C:\\Users\\zhangtao\\Desktop\\111\\666.xlsx');
        // await workbook_target.xlsx.writeFile(`C:\\Users\\zhangtao\\Desktop\\111\\终端1.xlsx`);
    } catch (err) {
        await querier.rollbackTransaction();
        console.log(err);
    } finally {
        querier.release();
    }
}

// updateplace();

checkcaiwu();